/*table hr group count*/
DECLARE @DATE_UPDATE NVARCHAR(100) = (SELECT [DATE_UPDATE] FROM [DATE_EM_REPORT])
declare @date date = convert(date,@date_update)
DECLARE @DATE_LAST_MONTH NVARCHAR(25) = CONVERT(NVARCHAR(25),DATEADD(m,-1,GETDATE()),112)
--PRINT(@DATE_LAST_MONTH)

delete from TBL_HR_GROUP_COUNT


--1. Group Deposit

INSERT INTO TBL_HR_GROUP_COUNT
SELECT A.DAO AS DAO,'1.Group Deposit', COUNT(DISTINCT(A.CIF)) AS TOTAL_CUSTOMER,0,0, 'deposit' AS GROUP_CODE
FROM TBL_CUSTOMER AS A, TBL_CM_DEPOSIT AS B
WHERE A.CIF=B.CIF and a.DAO is not null GROUP BY A.DAO

INSERT INTO TBL_HR_GROUP_COUNT
SELECT A.user_name AS DAO, '1.Group Deposit', COUNT(DISTINCT(c.CIF)) AS TOTAL_CUSTOMER,0,0, 'deposit' AS GROUP_CODE
FROM tbl_hr a, TBL_CUSTOMER b, TBL_CM_DEPOSIT c, TBL_BRANCH d
 where a.group_id = 3
 and b.CIF=c.CIF
 and a.branch_code_sme = d.BRANCH_CODE_SME
 and b.BRANCH_ID = d.BRANCH_ID
 group by a.dao, a.user_name, a.group_id, d.BRANCH_ID
 order by d.BRANCH_ID

INSERT INTO TBL_HR_GROUP_COUNT
SELECT A.user_name AS DAO, '1.Group Deposit', COUNT(DISTINCT(c.CIF)) AS TOTAL_CUSTOMER,0,0, 'deposit' AS GROUP_CODE
FROM tbl_hr a, TBL_CUSTOMER b, TBL_CM_DEPOSIT c, TBL_BRANCH d
where a.group_id = 2
and b.CIF=c.CIF
and a.branch_code_sme = d.REGION
and b.BRANCH_ID = d.BRANCH_ID
group by a.dao, a.user_name, a.group_id

INSERT INTO TBL_HR_GROUP_COUNT
SELECT A.user_name AS DAO, '1.Group Deposit', (select COUNT(distinct c.CIF) from TBL_CM_DEPOSIT a, TBL_BRANCH b, TBL_CUSTOMER c
where a.CIF = c.CIF and c.BRANCH_ID = b.BRANCH_ID) AS TOTAL_CUSTOMER,0,0, 'deposit' AS GROUP_CODE
FROM tbl_hr a
where a.group_id = 1


update TBL_HR_GROUP_COUNT
set NEW_BY_CIF= f.countcif
FROM TBL_HR_GROUP_COUNT as c join 
(select dao, count (DISTINCT(A.CIF)) as countcif 
from  TBL_CUSTOMER AS A
join TBL_CM_DEPOSIT AS B on A.CIF=B.CIF 
where a.DAO is not null and a.CUS_OPEN_DATE > @DATE_LAST_MONTH
GROUP BY A.DAO ) as f on f.dao=c.dao

--2.Group Loan
INSERT INTO TBL_HR_GROUP_COUNT
SELECT A.DAO AS DAO,'2.GROUP LOAN', COUNT(DISTINCT(A.CIF)) AS TOTAL_CUSTOMER,0,0, 'loan' AS GROUP_CODE
FROM TBL_CUSTOMER AS A, TBL_CM_LOAN AS B
WHERE A.CIF=B.CIF AND A.DAO IS NOT NULL AND B.CHANNEL<>'DSA' GROUP BY A.DAO


INSERT INTO TBL_HR_GROUP_COUNT
SELECT A.user_name AS DAO, '2.GROUP LOAN', COUNT(DISTINCT(c.CIF)) AS TOTAL_CUSTOMER,0,0, 'loan' AS GROUP_CODE
FROM tbl_hr a, TBL_CUSTOMER b, TBL_CM_LOAN c, TBL_BRANCH d
 where a.group_id = 3
 and b.CIF=c.CIF
 and a.branch_code_sme = d.BRANCH_CODE_SME
 and b.BRANCH_ID = d.BRANCH_ID
 and c.CHANNEL <> 'dsa'
 group by a.dao, a.user_name, a.group_id, d.BRANCH_ID
 order by d.BRANCH_ID

INSERT INTO TBL_HR_GROUP_COUNT
SELECT A.user_name AS DAO, '2.GROUP LOAN', COUNT(DISTINCT(c.CIF)) AS TOTAL_CUSTOMER,0,0, 'loan' AS GROUP_CODE
FROM tbl_hr a, TBL_CUSTOMER b, TBL_CM_LOAN c, TBL_BRANCH d
where a.group_id = 2
and b.CIF=c.CIF
and a.branch_code_sme = d.REGION
and b.BRANCH_ID = d.BRANCH_ID
 and c.CHANNEL <> 'dsa'
group by a.dao, a.user_name, a.group_id

INSERT INTO TBL_HR_GROUP_COUNT
SELECT A.user_name AS DAO, '2.GROUP LOAN', (select COUNT(distinct c.CIF)from TBL_CM_LOAN a, TBL_BRANCH b, TBL_CUSTOMER c
where a.CIF = c.CIF and c.BRANCH_ID = b.BRANCH_ID and a.CHANNEL <>'DSA') AS TOTAL_CUSTOMER,0,0, 'loan' AS GROUP_CODE
FROM tbl_hr a
where a.group_id = 1 

update TBL_HR_GROUP_COUNT
set NEW_BY_CIF= f.countcif
FROM TBL_HR_GROUP_COUNT as c join 
(select dao, count (DISTINCT(A.CIF)) as countcif 
from  TBL_CUSTOMER AS A
join TBL_CM_LOAN AS B on A.CIF=B.CIF 
where a.DAO is not null and a.CUS_OPEN_DATE > @DATE_LAST_MONTH
GROUP BY A.DAO ) as f on f.dao=c.dao where c.GROUP_CODE='loan'

UPDATE TBL_HR_GROUP_COUNT
SET NEW_BY_RULE= F.COUNTCIF
FROM TBL_HR_GROUP_COUNT AS C JOIN 
(SELECT A.DAO, COUNT (DISTINCT(A.CIF)) AS COUNTCIF --into #tam
FROM  TBL_CUSTOMER AS A
JOIN TBL_NEW_PR_LOAN AS B ON A.CIF=B.CIF 
WHERE A.DAO IS NOT NULL 
GROUP BY A.DAO ) AS F ON F.DAO=C.DAO WHERE C.GROUP_CODE='loan'

update a
set a.NEW_BY_RULE = b.countcif
from TBL_HR_GROUP_COUNT a, (select count(CIF) as countcif from TBL_NEW_PR_LOAN ) b, TBL_HR c
where a.DAO = c.USER_NAME and c.group_id = 1 and a.GROUP_CODE = 'loan'

update a
set a.NEW_BY_RULE = b.countcif
from TBL_HR_GROUP_COUNT a, 
(select count(a.CIF) as countcif, b.dao, b.DAO_NAME , c.REGION
from TBL_NEW_PR_LOAN a, TBL_HR b, TBL_BRANCH c
where a.BRANCH_CODE= c.BRANCH_ID and b.BRANCH_CODE_SME = c.REGION 
group by b.DAO, b.DAO_NAME, c.REGION) b, TBL_HR c
where a.DAO = c.USER_NAME and c.group_id = 2 and a.GROUP_CODE = 'loan' and b.DAO_NAME collate database_default = a.DAO

update a
set a.NEW_BY_RULE = b.countcif
from TBL_HR_GROUP_COUNT a, 
(select count(a.CIF) as countcif, b.dao, b.USER_NAME , c.BRANCH_CODE_SME,b.group_id
from TBL_NEW_PR_LOAN a, TBL_HR b, TBL_BRANCH c
where a.BRANCH_CODE= c.BRANCH_ID and b.BRANCH_CODE_SME = c.BRANCH_CODE_SME and b.group_id =3
group by b.DAO, b.USER_NAME, c.BRANCH_CODE_SME,b.group_id ) b
where a.GROUP_CODE = 'loan' and b.USER_NAME collate database_default = a.DAO

--select * from tbl_hr where group_id = 2

--select * from TBL_NEW_PR_LOAN

--3. Group Card

INSERT INTO TBL_HR_GROUP_COUNT
SELECT A.DAO AS DAO,'3.GROUP CARD', COUNT(DISTINCT(A.CIF)) AS TOTAL_CUSTOMER,0,0, 'card' AS GROUP_CODE
FROM TBL_CUSTOMER AS A, TBL_CM_CARD AS B
WHERE A.CIF=B.CIF AND A.DAO IS NOT NULL GROUP BY A.DAO

INSERT INTO TBL_HR_GROUP_COUNT
SELECT A.user_name AS DAO, '3.GROUP CARD', COUNT(DISTINCT(c.CIF)) AS TOTAL_CUSTOMER,0,0, 'card' AS GROUP_CODE
FROM tbl_hr a, TBL_CUSTOMER b, TBL_CM_CARD c, TBL_BRANCH d
 where a.group_id = 3
 and b.CIF=c.CIF
 and a.branch_code_sme = d.BRANCH_CODE_SME
 and b.BRANCH_ID = d.BRANCH_ID
 group by a.dao, a.user_name, a.group_id, d.BRANCH_ID
 order by d.BRANCH_ID

INSERT INTO TBL_HR_GROUP_COUNT
SELECT A.user_name AS DAO, '3.GROUP CARD', COUNT(DISTINCT(c.CIF)) AS TOTAL_CUSTOMER,0,0, 'card' AS GROUP_CODE
FROM tbl_hr a, TBL_CUSTOMER b, TBL_CM_CARD c, TBL_BRANCH d
where a.group_id = 2
and b.CIF=c.CIF
and a.branch_code_sme = d.REGION
and b.BRANCH_ID = d.BRANCH_ID
group by a.dao, a.user_name, a.group_id

INSERT INTO TBL_HR_GROUP_COUNT
SELECT A.user_name AS DAO, '3.GROUP CARD', (select COUNT(distinct c.CIF) from TBL_CM_CARD a, TBL_BRANCH b, TBL_CUSTOMER c
where a.CIF = c.CIF and c.BRANCH_ID = b.BRANCH_ID ) AS TOTAL_CUSTOMER,0,0, 'card' AS GROUP_CODE
FROM tbl_hr a
where a.group_id = 1



update TBL_HR_GROUP_COUNT
set NEW_BY_CIF= f.countcif
FROM TBL_HR_GROUP_COUNT as c join 
(select dao, count (DISTINCT(A.CIF)) as countcif 
from  TBL_CUSTOMER AS A
join TBL_CM_CARD AS B on A.CIF=B.CIF 
where a.DAO is not null and a.CUS_OPEN_DATE > @DATE_LAST_MONTH
GROUP BY A.DAO ) as f on f.dao=c.dao where c.GROUP_CODE='card'

INSERT INTO TBL_HR_GROUP_COUNT
SELECT A.DAO AS DAO,'4.GROUP POS', COUNT(DISTINCT(A.CIF)) AS TOTAL_CUSTOMER,0,0, 'pos' AS GROUP_CODE
FROM TBL_CUSTOMER AS A, TBL_CM_POS AS B
WHERE A.CIF=B.CIF AND A.DAO IS NOT NULL GROUP BY A.DAO


INSERT INTO TBL_HR_GROUP_COUNT
SELECT A.user_name AS DAO, '4.GROUP POS', COUNT(DISTINCT(c.CIF)) AS TOTAL_CUSTOMER,0,0, 'pos' AS GROUP_CODE
FROM tbl_hr a, TBL_CUSTOMER b, TBL_CM_POS c, TBL_BRANCH d
 where a.group_id = 3
 and b.CIF=c.CIF
 and a.branch_code_sme = d.BRANCH_CODE_SME
 and b.BRANCH_ID = d.BRANCH_ID
 group by a.dao, a.user_name, a.group_id, d.BRANCH_ID
 order by d.BRANCH_ID

INSERT INTO TBL_HR_GROUP_COUNT
SELECT A.user_name AS DAO, '4.GROUP POS', COUNT(DISTINCT(c.CIF)) AS TOTAL_CUSTOMER,0,0, 'pos' AS GROUP_CODE
FROM tbl_hr a, TBL_CUSTOMER b, TBL_CM_POS c, TBL_BRANCH d
where a.group_id = 2
and b.CIF=c.CIF
and a.branch_code_sme = d.REGION
and b.BRANCH_ID = d.BRANCH_ID
group by a.dao, a.user_name, a.group_id

INSERT INTO TBL_HR_GROUP_COUNT
SELECT A.user_name AS DAO, '4.GROUP POS', (select COUNT(distinct c.CIF) from TBL_CM_POS a, TBL_BRANCH b, TBL_CUSTOMER c
where a.CIF = c.CIF and c.BRANCH_ID = b.BRANCH_ID ) AS TOTAL_CUSTOMER,0,0, 'pos' AS GROUP_CODE
FROM tbl_hr a
where a.group_id = 1

update TBL_HR_GROUP_COUNT
set NEW_BY_CIF= f.countcif
FROM TBL_HR_GROUP_COUNT as c join 
(select dao, count (DISTINCT(A.CIF)) as countcif 
from  TBL_CUSTOMER AS A
join TBL_CM_POS AS B on A.CIF=B.CIF 
where a.DAO is not null and a.CUS_OPEN_DATE > @DATE_LAST_MONTH
GROUP BY A.DAO ) as f on f.dao=c.dao where c.GROUP_CODE='pos'

--5. group i2b count

INSERT INTO TBL_HR_GROUP_COUNT
SELECT A.DAO AS DAO,'5.GROUP I2B', COUNT(DISTINCT(A.CIF)) AS TOTAL_CUSTOMER,0,0, 'i2b' AS GROUP_CODE
FROM TBL_CUSTOMER AS A, TBL_CM_I2B AS B
WHERE A.CIF=B.CIF AND A.DAO IS NOT NULL GROUP BY A.DAO

INSERT INTO TBL_HR_GROUP_COUNT
SELECT A.user_name AS DAO, '5.GROUP I2B', COUNT(DISTINCT(c.CIF)) AS TOTAL_CUSTOMER,0,0, 'i2b' AS GROUP_CODE
FROM tbl_hr a, TBL_CUSTOMER b, TBL_CM_I2B c, TBL_BRANCH d
 where a.group_id = 3
 and b.CIF=c.CIF
 and a.branch_code_sme = d.BRANCH_CODE_SME
 and b.BRANCH_ID = d.BRANCH_ID
 group by a.dao, a.user_name, a.group_id, d.BRANCH_ID
 order by d.BRANCH_ID

INSERT INTO TBL_HR_GROUP_COUNT
SELECT A.user_name AS DAO, '5.GROUP I2B', COUNT(DISTINCT(c.CIF)) AS TOTAL_CUSTOMER,0,0, 'i2b' AS GROUP_CODE
FROM tbl_hr a, TBL_CUSTOMER b, TBL_CM_I2B c, TBL_BRANCH d
where a.group_id = 2
and b.CIF=c.CIF
and a.branch_code_sme = d.REGION
and b.BRANCH_ID = d.BRANCH_ID
group by a.dao, a.user_name, a.group_id

INSERT INTO TBL_HR_GROUP_COUNT
SELECT A.user_name AS DAO, '5.GROUP I2B', (select COUNT(distinct CIF) from TBL_CM_I2B) AS TOTAL_CUSTOMER,0,0, 'i2b' AS GROUP_CODE
FROM tbl_hr a
where a.group_id = 1


--SELECT CIF, LAST_OPEN_DATE, BRANCH_ID
--FROM TBL_CUSTOMER A, 
--(SELECT COUNT(CUST_CIF) AS COUNT_ACC, CUST_CIF, MAX(OPEN_DATE) AS LAST_OPEN_DATE 
--FROM TBL_CM_I2B_DETAIL A 
--WHERE OPEN_DATE IS NOT NULL 
--GROUP BY CUST_CIF) B
--WHERE A.CIF = B.CUST_CIF

update TBL_HR_GROUP_COUNT
set NEW_BY_CIF= f.countcif
FROM TBL_HR_GROUP_COUNT as c join 
(select a.DAO, count (DISTINCT(A.CIF)) as countcif 
from  TBL_CUSTOMER AS A
join TBL_CM_I2B AS B on A.CIF=B.CIF 
where a.DAO is not null and a.CUS_OPEN_DATE > @DATE_LAST_MONTH
GROUP BY A.DAO ) as f on f.dao=c.dao where c.GROUP_CODE='i2b'

INSERT TBL_HR_GROUP_COUNT
  SELECT DAO, '6. GROUP TRADE', COUNT(CIF), 0 , 0, 'trade' FROM TBL_CM_TRADE where dao is not null
  GROUP BY DAO

INSERT INTO TBL_HR_GROUP_COUNT
SELECT A.user_name AS DAO, '6.GROUP TRADE', COUNT(DISTINCT(c.CIF)) AS TOTAL_CUSTOMER,0,0, 'trade' AS GROUP_CODE
FROM tbl_hr a, TBL_CUSTOMER b, TBL_CM_TRADE c, TBL_BRANCH d
 where a.group_id = 3
 and b.CIF=c.CIF
 and a.branch_code_sme = d.BRANCH_CODE_SME
 and b.BRANCH_ID = d.BRANCH_ID
 group by a.dao, a.user_name, a.group_id, d.BRANCH_ID
 order by d.BRANCH_ID

INSERT INTO TBL_HR_GROUP_COUNT
SELECT A.user_name AS DAO, '6.GROUP TRADE', COUNT(DISTINCT(c.CIF)) AS TOTAL_CUSTOMER,0,0, 'trade' AS GROUP_CODE
FROM tbl_hr a, TBL_CUSTOMER b, TBL_CM_TRADE c, TBL_BRANCH d
where a.group_id = 2
and b.CIF=c.CIF
and a.branch_code_sme = d.REGION
and b.BRANCH_ID = d.BRANCH_ID
group by a.dao, a.user_name, a.group_id

INSERT INTO TBL_HR_GROUP_COUNT
SELECT A.user_name AS DAO, '6.GROUP TRADE', (select COUNT(distinct CIF) from TBL_CM_TRADE) AS TOTAL_CUSTOMER,0,0, 'trade' AS GROUP_CODE
FROM tbl_hr a
where a.group_id = 1

  update  b
  set NEW_BY_RULE =a.counts
  from (select count(cif) as counts, dao from TBL_CM_TRADE where new_cust_status = 1 group by DAO) as a, TBL_HR_GROUP_COUNT b
  where a.DAO=b.DAO and b.GROUP_CODE = 'trade'


INSERT INTO TBL_HR_GROUP_COUNT
SELECT A.DAO AS DAO,'7.Group Chuoi', COUNT(DISTINCT(A.CIF)) AS TOTAL_CUSTOMER,0,0, 'chuoi' AS GROUP_CODE
FROM TBL_CUSTOMER AS A, TBL_TT_CHUOI AS B
WHERE A.CIF=B.cust_id and a.DAO is not null GROUP BY A.DAO

INSERT INTO TBL_HR_GROUP_COUNT
SELECT A.user_name AS DAO, '7.Group Chuoi', COUNT(DISTINCT(c.cust_id)) AS TOTAL_CUSTOMER,0,0, 'chuoi' AS GROUP_CODE
FROM tbl_hr a, TBL_CUSTOMER b, TBL_TT_CHUOI c, TBL_BRANCH d
 where a.group_id = 3
 and b.CIF=c.cust_id
 and a.branch_code_sme = d.BRANCH_CODE_SME
 and b.BRANCH_ID = d.BRANCH_ID
 group by a.dao, a.user_name, a.group_id, d.BRANCH_ID
 order by d.BRANCH_ID

INSERT INTO TBL_HR_GROUP_COUNT
SELECT A.USER_NAME AS DAO, '7.Group Chuoi', COUNT(DISTINCT(c.cust_id)) AS TOTAL_CUSTOMER,0,0, 'chuoi' AS GROUP_CODE
FROM tbl_hr a, TBL_CUSTOMER b, TBL_TT_CHUOI c, TBL_BRANCH d
where a.group_id = 2
and b.CIF=c.cust_id
and a.branch_code_sme = d.REGION
and b.BRANCH_ID = d.BRANCH_ID
group by a.dao, a.user_name, a.group_id

INSERT INTO TBL_HR_GROUP_COUNT
SELECT A.user_name AS DAO, '7.Group Chuoi', (select COUNT(distinct cust_id) from TBL_TT_CHUOI) AS TOTAL_CUSTOMER,0,0, 'chuoi' AS GROUP_CODE
FROM tbl_hr a
where a.group_id = 1


--select * from TBL_HR
--select * from TBL_BRANCH

--delete from server12.SMECustomer360.[dbo].TBL_HR_GROUP_COUNT
--insert server12.SMECustomer360.dbo.TBL_HR_GROUP_COUNT
--select * from TBL_HR_GROUP_COUNT where dao like '%test%'

